与技术谈一场永不分手的恋爱[送书活动]

作者：superZS（老张）

原文：http://blog.51cto.com/sumongodb/1949800

注：民工哥做了一些修改

找到一份合适的工作，就像在工作的八小时之内有了一个心仪的恋人。

老张我呢，原来在上学那会儿，不是特别爱学习，本人长得呢也特别帅（现在做了技术可惜了哈哈！）所以特招漂亮女孩的喜欢，当然自己也特别喜欢和她们谈恋爱啦，那感觉老甜蜜了呢。也算得上是情感专家。但是最后都没啥好结果，弄得我今后都不再相信爱情了。

生活中谈恋爱是这样，那研究技术也同样如此。一沉不变的技术知识，时间长了，我们都会觉得枯燥无味，索然无趣。需要新鲜的血液进入到我们的神经中枢，激发我们对于求知欲的渴望。

今儿跟大家梳理一下MySQL数据库中 5.7这个版本的新特性，让那些平时只顾着工作,没时间去研究新知识的同学，可以看到数据库的新功能，新特性，今后有利用应用到生产中，更便于我们开展工作。让我们把这个恋爱谈得更长久，更甜蜜。

目前互联网公司，线上mysql用的最多的版本是mysql5.5,5.6,5.7。先看下三个版本的性能对比图：

OLTP READ ONLY

MySQL 5.7比MySQL5.6 快近似于2倍

比MySQL5.5快近似于3倍

OLTP READ WRITE

MySQL5.7比MySQL5.6快近似于1.5倍

比MySQL5.5快近似于2.5倍

MySQL数据库，在5这个版本上待了10年之久，server层没有太大的变化，主要是存储引擎层的改变。也就是目前最火的Innodb存储引擎，无论从它的功能上、还是性能上，都有所提高。

一.    先从Innodb角度介绍

Innodb的增强分为三大部分：

1.功能提升

1.  Online Alter table；

2.  Innodb_buffer_pool online change;

3. 原子写特性的检测；可以关闭double_write

4. Innodb buffer pool dump

2.性能提升

1. Innodb临时表的DDL性能提升;临时表不需要再记录redo log；
   

2. Read Only性能上面的提升；

3. page cleaner线程数量上的增加，提升innodb_page_cleaners的效率
   

3.其他点提升

1. 截断undo特性；

2. 支持分区表的Transportable Tablespaces功能

下面详细逐一介绍

Online Alter table

MySQL 5.7支持重命名索引和修改varchar的大小，无需table-copy。这两项操作在之前的版本中，都需要重建索引或表。适用于各引擎。

例如如下：

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);

但存在限制，即只支持0~255字节内的或者255以上字节间的增加，也就是说若从254增到256时不能使用INPLACE算法，必须使用COPY算法，否侧报错。这是因为0~255内的VARCHAR值需要一个额外的字节来编码，而256以上的VARCHAR值需要两个字节来编码。另外使用INPLACE算法缩小VARCHAR的ALTER TABLE也是不支持的，必须用COPY算法。

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

动态修改Buffer Pool

动态调整innodb_buffer_pool_size大小，且不会消耗太高的代价。新增配置参数innodb_buffer_pool_chunk_size用来设置chunk的大小，新引入chunk的概念，每个chunk默认是128M，以及新增状态参数Innodb_buffer_pool_resize_status用来监视buffer pool的resize过程。

TIPS：从小改到大基本对服务没有什么影响，从大到改到小，就是多了一步需要释放内存，但可以忽略到影响。

Innodb buffer pool dump

新增参数innodb_buffer_pool_dump_pct，支持仅dump每个缓冲池中最热的 m% 页，即为最热的数据page。如果在业务高峰时发生宕机，数据库在恢复重启时，可以快速把热数据导入内存，避免血崩。load操作改进，减少了对用户活动的破坏性，减少IO资源占用。

默认如下两个参数都是开启的状态

innodb_buffer_pool_dump_at_shutdown=on
innodb_buffer_pool_load_at_startup=on

在系统负载过高的时候，会根据innodb_io_capacity这个参数来设置 dump的速度，io压力不大的时候可以适当调大一些。

page cleaner数量的增加

支持多个page cleaner线程从buffer pool中刷新脏页，可以适当调整innodb_page_cleaners配置线程数，其默认值为1。

截断undo特性

原来undo log在ibdata1中，多余的undo log容易使共享表空间文件暴涨，占用过多的磁盘空间。mysql5.6之后可以把undo log从ibdata1中分离出来，成为独立的表空间。5.7版本

通过新增的配置选项innodb_undo_log_truncate启用,并由参数innodb_max_undo_log_size指定截断阈值，当undo log超过阈值时截断已回收的undo log，防止磁盘空间紧张。

http://dev.mysql.com/doc/refman/5.7/en/truncate-undo-tablespace.html

二. 复制（replication）功能的提升

1. 并行复制

2. 多源复制

3. 增强半同步
   

4. 组复制--MGR

并行复制：基于logical-clock（5.7引入）一个组提交内事务都可以并行，可以达到接近主库并发效果。

进入prepare状态的事务都可以在slave并行应用。

多源复制：支持由多个master向一个slave复制。用于将多个服务器备份到单个服务器上。可用于异地容灾，集中备份。

增强半同步：是在mysql5.5半同步复制基础上的增强，在集群架构切换时可以保证数据的一致性。由after_commit变成了after_sync，也提高了复制的效率。

组复制：有点像Oracle里面的RAC集群，可以保证多节点并行写入数据。比较类似于PXC架构。个人建议：目前不是很成熟，先不建议使用。

三. SYS schema功能的增强

sys schema是MySQL 5.7.7中引入的一个系统库，包含了一些视图和函数。不同于之前的版本，需要通过DBA的经验之谈去排查数据库的问题，我们可以通过sys schema了解到，哪些语句使用了临时表，哪个用户请求了最多的io，哪个线程占用了最多的内存，哪些索引是无用索引。

查看数据库中的冗余索引的SQL语句：

select * from sys.schema_redundant_indexes;

查看数据库中的索引使用情况语句：

select index_name,rows_selected,rows_inserted,rows_deleted,rows_updated from schema_index_statistics where table_schema='DB_name' and table_name='table**' 
and index_name='**';

查看数据库的未使用到的索引：

select * from sys.schema_unused_indexes;

查看数据库IO写数据文件的最多10条（TOP 10）

select * from sys.x$io_global_by_file_by_bytes order by total_written desc limit 10;

查看实例消耗的内存：

select * from sys.memory_global_total;

四.优化器的提升：

mysql5.7之前，mysql的优化器很垃圾，是性能瓶颈点。5.7之后改善了很多功能。

1. UNION ALL查询的优化。尽量避免UNION ALL语句创建临时表，并提高了响应速度。

2. 新增了更多的HINTS，并提供新的HINTS语法。

3. EXPLAIN FOR CONNECTION 。能获取一个指定会话中正在执行的SQL语句的执行计划，而不需要找到SQL语句。

4. 排序效率上面的提升。

5. IN子查询的提升。

6. JSON格式的输出结果中，还能看到执行计划的代价信息。

五.设置查询sql的超时(max_execution_time)

MySQL 5.7.4刚引入名字是max_statement_time，后来改成max_execution_time。这个参数很实用，是一种自我保护的措施。防止因为一条sql语句的长时间执行，导致数据库血崩。

六.安全性的增强

1. mysql_install_db弃用了，改用mysqld加–initialize或–initialize-insecure。

2. 不再使用password字段，使用authentication_string替代。

3. MySQL 5.7开始，root用户的密码不再是空的了，而是随机产生一个，保存于error log，初次登录需使用密码并修改密码。

4. mysql.user表新增plugin列，且若某账户该字段值为空则账户不能使用。

七.sql_mode参数

由之前的NO_ENGINE_SUBSTITUTION变成为严格模式STRICT_TRANS_TABLES SQL mode。

如出现sql书写有问题的，直接抛出错误。不会再出现截断等现象。也不能在grant的过程中，直接创建用户了。

福利专区

此次民工哥联合博文视点给大家带来MySQL又一精典书籍福利

《MySQL王者晋级之路》深入剖析MySQL数据库体系结构，实战演练备份恢复、主从复制，详解高可用集群架构的设计与实践过程，详细梳理优化思路，展现新版本的特性，并与真实生产案例相结合，通过核心原理到“王者”实战，全面覆盖MySQL数据库的知识点。

《MySQL王者晋级之路》适合熟悉Linux系统且想提升MySQL水平的读者。

觉的书不错扫码直接达

送书规则

1、此活动仅限公众号关注者参加（无关注无效）

2、文章末尾留言写出你与MySQL数据库之间相爱、相杀的故事，精选留言点赞前三名各送出一本（刷赞无效）

3、在精选留言中抽出一位一直以来关注、支持公众号的小伙伴送出一本

4、在精选留言中随机抽出一位幸运吃瓜群众送出一本

5、此次活动截止时间2018年4月21日12:00

6、用心写留言被精选的概率就大并且被选中送书的概率就会再次增大

7、请中书的小伙伴在一个工作日内后台或加民工哥微信(ken_chu1985)发送收货信息，逾期视为放弃

推荐阅读

·end·

—如果喜欢，快分享给你的朋友们吧—

我们一起愉快的玩耍吧

超过20000+人正在关注

专注于Linux系统运维、运维架构、各类高可用架构技术，热衷于分享开源技术，热爱开源、拥抱开源，我是80后民工哥，微信公众号：民工哥Linux运维，我为自己带盐，谁说80后不能活出精彩人生！！！

关注公众号后台回复 “入群” 索取入群方式

长按关注公众号
热爱开源、拥抱开源